package org.mch.func.db.tool;

import java.io.Serializable;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.mch.comm.db.exphandler.ExpRSBaseEOHandler;
import org.mch.comm.db.exphandler.ExpRSEasyPOIEOHandler;
import org.mch.comm.db.exphandler.ExpRSInsertSQLHandler;
import org.mch.comm.db.exphandler.ExpRSJPABeanHandler;
import org.mch.comm.db.exphandler.ExpRSJavaBeanHandler;
import org.mch.comm.db.exphandler.ExpRSSuperVOHandler;
import org.mch.comm.db.mhandler.GenerateHibernateEOHandler;
import org.mch.func.cons.ConsDB;
import org.mch.func.entity.TableColumnEO;

/**
 * 描述：数据库操作类；
 * 
 * @author MCH；Date：2008-10-01；<BR/>
 * 
 */
public class DBHandle implements Serializable {
	private static final long serialVersionUID = 1L;

	public DBHandle() {
	}

	public String getSuperVO(String tableName) throws Exception {
		String sql = "SELECT * FROM " + tableName + " WHERE 1=2";
		QueryRunner run = new QueryRunner();
		return (String) run.query(ConsDB.conn, sql, new ExpRSSuperVOHandler(ConsDB.connInfo.getDbtype(), tableName));
	}

	public String getHibernateVO(String tableName) throws Exception {
		GenerateHibernateEOHandler handle = new GenerateHibernateEOHandler(tableName);
		return handle.handle();
	}

	public String getJavaBean(String tableName) throws Exception {
		String sql = "SELECT * FROM " + tableName + " WHERE 1=2";
		QueryRunner run = new QueryRunner();
		return (String) run.query(ConsDB.conn, sql, new ExpRSJavaBeanHandler(ConsDB.connInfo.getDbtype(), tableName));
	}

	public String getEasyPOIEO(String tableName) throws Exception {
		String sql = "SELECT * FROM " + tableName + " WHERE 1=2";
		QueryRunner run = new QueryRunner();
		return (String) run.query(ConsDB.conn, sql, new ExpRSEasyPOIEOHandler(ConsDB.connInfo.getDbtype(), tableName));
	}

	public String getBaseEO(String tableName) throws Exception {
		String sql = "SELECT * FROM " + tableName + " WHERE 1=2";
		QueryRunner run = new QueryRunner();
		return (String) run.query(ConsDB.conn, sql, new ExpRSBaseEOHandler(ConsDB.connInfo.getDbtype(), tableName));
	}

	public String getJPABean(String tableName) throws Exception {
		String sql = "SELECT * FROM " + tableName + " WHERE 1=2";
		QueryRunner run = new QueryRunner();
		return (String) run.query(ConsDB.conn, sql, new ExpRSJPABeanHandler(ConsDB.connInfo.getDbtype(), tableName));
	}

	public String getInsertSQL(String sql, String tableName, boolean isDeleteSQL, String deletefield) throws Exception {
		QueryRunner run = new QueryRunner();
		return (String) run.query(ConsDB.conn, sql, new ExpRSInsertSQLHandler(ConsDB.connInfo.getDbtype(), tableName, isDeleteSQL, deletefield));
	}

	/**
	 * 根据表名，返回表预编译插入SQL；
	 * 
	 * @param tablename
	 * @return
	 * @throws Exception
	 */
	public String getInsertYbjSQL(String tablename, boolean isnotpk, boolean islower) throws Exception {
		Map<String, TableColumnEO> colmap = DBInfoUtil.getColmnTypes(tablename);
		String[] cols = colmap.keySet().toArray(new String[0]);
		String pkfieldname = DBInfoUtil.getTablePKField(tablename);

		StringBuffer insert = new StringBuffer("");
		StringBuffer values = new StringBuffer("VALUES(");

		insert.append("INSERT INTO ");
		insert.append(islower ? tablename.toLowerCase() : tablename);
		insert.append("(");
		for (int i = 0, len = cols.length; i < len; i++) {
			String colname = islower ? cols[i].toLowerCase() : cols[i];

			if (isnotpk && pkfieldname.equalsIgnoreCase(colname)) {
				continue;
			}

			insert.append(colname).append(",");
			values.append("?,");
		}
		insert.delete(insert.length() - 1, insert.length());
		values.delete(values.length() - 1, values.length());

		values.append(") ");
		insert.append(") \n").append(values).append("\n");
		return insert.toString();
	}

	public String getUpdateYbjSQL(String tablename, boolean islower) throws Exception {
		Map<String, TableColumnEO> colmap = DBInfoUtil.getColmnTypes(tablename);
		String[] cols = colmap.keySet().toArray(new String[0]);
		String pkfieldname = DBInfoUtil.getTablePKField(tablename);

		StringBuffer update = new StringBuffer("");

		update.append("UPDATE ");
		update.append(islower ? tablename.toLowerCase() : tablename);
		update.append(" SET ");
		for (int i = 0, len = cols.length; i < len; i++) {
			String colname = islower ? cols[i].toLowerCase() : cols[i];

			if (pkfieldname.equalsIgnoreCase(colname)) {
				continue;
			}

			update.append(colname).append("=?,");
		}
		update.delete(update.length() - 1, update.length());

		update.append(" WHERE ").append(pkfieldname).append("=?");
		return update.toString();
	}

	/**
	 * 根据表名，返回表MyBatis插入SQL；
	 * 
	 * @param tablename
	 * @return
	 * @throws Exception
	 */
	public String getInsertMyBatisSQL(String tablename, boolean isnotpk, boolean islower) throws Exception {
		Map<String, TableColumnEO> colmap = DBInfoUtil.getColmnTypes(tablename);
		String[] cols = colmap.keySet().toArray(new String[0]);
		String pkfieldname = DBInfoUtil.getTablePKField(tablename);

		StringBuffer insert = new StringBuffer("");
		StringBuffer values = new StringBuffer("VALUES(");

		insert.append("INSERT INTO ");
		insert.append(islower ? tablename.toLowerCase() : tablename);
		insert.append("(");
		for (int i = 0, len = cols.length; i < len; i++) {
			String colname = islower ? cols[i].toLowerCase() : cols[i];

			if (isnotpk && pkfieldname.equalsIgnoreCase(colname)) {
				continue;
			}

			insert.append(colname).append(",");
			values.append("#{" + colname + "},");
		}
		insert.delete(insert.length() - 1, insert.length());
		values.delete(values.length() - 1, values.length());

		values.append(") ");
		insert.append(") \n").append(values).append("\n");
		return insert.toString();
	}

	public String getUpdateMyBatisSQL(String tablename, boolean islower) throws Exception {
		Map<String, TableColumnEO> colmap = DBInfoUtil.getColmnTypes(tablename);
		String[] cols = colmap.keySet().toArray(new String[0]);
		String pkfieldname = DBInfoUtil.getTablePKField(tablename);

		StringBuffer update = new StringBuffer("");

		update.append("UPDATE ");
		update.append(islower ? tablename.toLowerCase() : tablename);
		update.append(" SET ");
		for (int i = 0, len = cols.length; i < len; i++) {
			String colname = islower ? cols[i].toLowerCase() : cols[i];

			if (pkfieldname.equalsIgnoreCase(colname)) {
				continue;
			}

			update.append(colname).append("=#{" + colname + "},");
		}
		update.delete(update.length() - 1, update.length());

		update.append(" WHERE ").append(pkfieldname).append("=#{" + pkfieldname + "}");
		return update.toString();
	}
}